package poi;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

public class Test {
    public static void handleExcel(String sourcePath,String targetPath) throws IOException, InvalidFormatException {
        //输入流，读取，关闭
        FileInputStream fis = new FileInputStream(sourcePath);
        Workbook workbook = WorkbookFactory.create(fis);
        fis.close();

        //准备输出流
        FileOutputStream fos = new FileOutputStream(targetPath);

        //取第二个sheet
        Sheet sheet = workbook.getSheetAt(1);
        for (Row row : sheet) {
            if (row.getRowNum() == sheet.getFirstRowNum()) {//首行不读取
                continue;
            }else if(row.getRowNum() == sheet.getLastRowNum()){//最后一行
                break;
            }else{
                System.out.println("=====================读取第"+(row.getRowNum()+1)+"行=====================");
                Cell cell = row.getCell(1);//获取第2个单元格

                String cellValue = getExcelValue(cell);
                System.out.println("读取数据："+cellValue);
                String newValue = String.format("%04d", Integer.valueOf(cellValue));
                System.out.println("操作后数据："+newValue);

                if(row.getCell(8) == null){//数据放到第9个单元格
                    row.createCell(8).setCellValue(newValue);
                }else{
                    row.getCell(8).setCellValue(newValue);
                }

            }
        }
        workbook.write(fos);//修改后的数据输出到原文件中
        fos.flush();
        fos.close();
    }

    private static String getExcelValue(Cell cell) {
        String ret = "";
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                    ret = formater.format(date);
                } else if (String.valueOf(cell.getNumericCellValue()).contains(".")) {
                    DecimalFormat df = new DecimalFormat("#");
                    ret = df.format(cell.getNumericCellValue());
                } else {
                    ret = (cell + "").trim();
                }
                break;
            default:
                ret = (cell + "").trim();
                break;
        }
        return ret;
    }

    public static void main(String[] args) throws IOException, InvalidFormatException {
        String filePath = "C:\\Users\\tzs\\Desktop\\中交项目业务分类（可入库）.xls";
        String targetPath = "C:\\Users\\tzs\\Desktop\\中交项目业务分类（可入库） - 副本.xls";
        handleExcel(filePath,targetPath);
    }
}
